/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.ssau.model.storing;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Locale;
import org.jdom.Element;
import ru.ssau.entities.AuthorizationInfo;
import ru.ssau.entities.Message;
import ru.ssau.entities.Thread;
import ru.ssau.entities.User;

/**
 *
 * @author Desperus
 */
public class ForumDatabaseDAO implements ForumDataAccessObject {

    private static final String connectionInfo = "jdbc:oracle:thin:@localhost:1521:XE";
    private static final String dbLogin = "system";
    private static final String dbPass = "pass";
    //запросы
    //Threads
    private static final String requestThreadAdd =
            "INSERT INTO Threads(ThreadID,Caption) VALUES(?,?)";
    private static final String requestThreadSequenceGetId =
            "SELECT ThreadIDSeq.NEXTVAL FROM dual";
    private static final String requestThreadUpdate =
            "UPDATE Threads SET Caption=? WHERE ThreadID=?";
    private static final String requestThreadGetById =
            "SELECT Caption FROM Threads WHERE ThreadId=?";
    private static final String requestThreadDeleteById =
            "DELETE FROM Threads WHERE ThreadID=?";
    private static final String requestThreadsGetAll =
            "SELECT ThreadID, Caption FROM Threads";
    //Users
    private static final String requestUserAdd =
            "INSERT INTO Users(UserID,UserName,RegDate,UserInfo) VALUES(?,?,?,?)";
    private static final String requestUserSequenceGetId =
            "SELECT UserIDSeq.NEXTVAL FROM dual";
    private static final String requestUserDeleteById =
            "DELETE FROM Users WHERE UserID=?";
    private static final String requestUserGetById =
            "SELECT UserName,RegDate,UserInfo FROM Users WHERE UserId=?";
    private static final String requestUserUpdate =
            "UPDATE Users SET UserName=?, RegDate=?, UserInfo=? "
            + "WHERE UserId=?";
    private static final String requestUsersGetAll =
            "SELECT UserId,UserName,RegDate,UserInfo FROM Users";
    //AuthorizationInfo
    private static final String requestAuthorizationInfoAdd =
            "INSERT INTO AuthorizationInfo(AuthorizationID,UserID,Login,Pass) VALUES(?,?,?,?)";
    private static final String requestAuthorizationInfoDeleteById =
            "DELETE FROM AuthorizationInfo WHERE AuthorizationID=?";
    private static final String requestAuthorizationInfoSequenceGetId =
            "SELECT AuthInfoIDSeq.NEXTVAL FROM dual";
    private static final String requestAuthorizationInfoGetById =
            "SELECT UserId,Login,Pass FROM AuthorizationInfo WHERE AuthorizationId=?";
    private static final String requestAuthorizationInfoGetByUserId =
            "SELECT AuthorizationId,Login,Pass FROM AuthorizationInfo "
            + "WHERE UserId=?";
    private static final String requestAuthorizationInfoUpdate =
            "UPDATE AuthorizationInfo SET UserId=?, Login=?, "
            + "Pass=? WHERE AuthorizationId=?";
    //Messages
    private static final String requestMessageSequenceGetId =
            "SELECT MessageIDSeq.NEXTVAL FROM dual";
    private static final String requestMessageAdd =
            "INSERT INTO Messages(MessageId,MsgCaption,MsgText,"
            + "UserId,ThreadId,PostTime) VALUES(?,?,?,?,?,?)";
    private static final String requestMessageUpdate =
            "UPDATE Messages SET MsgCaption=?,MsgText=?,"
            + "UserId=?,ThreadId=?,PostTime=? WHERE MessageId=?";
    private static final String requestMessageDeleteById =
            "DELETE FROM Messages WHERE MessageId=?";
    private static final String requestMessageGetById =
            "SELECT MsgCaption,MsgText,UserId,ThreadId,PostTime "
            + "FROM Messages WHERE MessageId=?";
    private static final String requestMessagesGetAll =
            "SELECT MessageId,MsgCaption,MsgText,UserId,ThreadId,PostTime "
            + "FROM Messages";
    //имена сиквенсов в бд
    //private static final String sequenceThreadId = "ThreadIDSeq";
    private Connection conn;

    public ForumDatabaseDAO() throws DataAccessException {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Locale.setDefault(Locale.ENGLISH);
            conn = DriverManager.getConnection(connectionInfo, dbLogin, dbPass);
            conn.setAutoCommit(false);
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } catch (ClassNotFoundException ex) {
            throw new DataAccessException(ex);
        }
    }

    private Integer generateThreadId() throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestThreadSequenceGetId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return null;
    }

    public void addThread(Thread thread) throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestThreadAdd);
            int id = generateThreadId();
            ps.setInt(1, id);
            ps.setString(2, thread.getCaption());
            ps.executeUpdate();
            conn.commit();
            thread.setId(id);
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

    public void replaceThread(Thread thread) throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestThreadUpdate);
            ps.setString(1, thread.getCaption());
            ps.setInt(2, thread.getId());
            ps.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

    public Thread getThreadById(Integer id) throws DataAccessException {
        Thread thread = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestThreadGetById);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                thread = new Thread(id, rs.getString("Caption"));
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return thread;
    }

    public void deleteThreadById(Integer id) throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestThreadDeleteById);
            ps.setInt(1, id);
            ps.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

    //если ничего нет, то возвращается пустая коллекция
    public Collection<Thread> getThreads() throws DataAccessException {
        ArrayList<Thread> result = new ArrayList<Thread>();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestThreadsGetAll);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Thread thread = new Thread(rs.getInt("ThreadId"), rs.getString("Caption"));
                result.add(thread);
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return result;
    }

    private Integer generateMessageId() throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestMessageSequenceGetId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return null;
    }

    public void addMessage(Message message) throws DataAccessException {
     PreparedStatement ps=null;
     try {
         ps=conn.prepareStatement(requestMessageAdd);
         Integer id=generateMessageId();
         ps.setInt(1,id);
         ps.setString(2, message.getCaption());
         ps.setString(3, message.getText());
         ps.setInt(4,message.getUserId());
         ps.setInt(5, message.getThreadId());
         Timestamp time=new Timestamp(message.getPostDate().getTime());
         ps.setTimestamp(6, time);
         ps.executeUpdate();
         
         conn.commit();
         message.setId(id);
     } catch(SQLException ex) {
         throw new DataAccessException(ex);
     } finally {
         closeStatement(ps);
     }
    }

    public void replaceMessage(Message message) throws DataAccessException {
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(requestMessageUpdate);
            ps.setString(1, message.getCaption());
            ps.setString(2, message.getText());
            ps.setInt(3, message.getUserId());
            ps.setInt(4, message.getThreadId());
            Timestamp stamp=new Timestamp(message.getPostDate().getTime());
            ps.setTimestamp(5, stamp);
            ps.setInt(6, message.getId());
            ps.executeUpdate();
            
            conn.commit();
        } catch(SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

    public Message getMessageById(Integer id) throws DataAccessException {
        PreparedStatement ps=null;
        Message message=null;
        try {
            ps=conn.prepareStatement(requestMessageGetById);
            ps.setInt(1, id);
            ResultSet rs=ps.executeQuery();
            if(rs.next()) {
                message=new Message();
                message.setId(id);
                message.setCaption(rs.getString("MsgCaption"));
                message.setText(rs.getString("MsgText"));
                message.setUserId(rs.getInt("UserId"));
                message.setThreadId(rs.getInt("ThreadId"));
                java.util.Date date=new java.util.Date(
                        rs.getTimestamp("PostTime").getTime());
                message.setPostDate(date);
            }
            
            conn.commit();
        } catch(SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return message;
    }

    public void deleteMessageById(Integer id) throws DataAccessException {
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(requestMessageDeleteById);
            ps.setInt(1, id);
            ps.executeUpdate();

            conn.commit();
        } catch(SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

    public Collection<Message> getMessages() throws DataAccessException {
        ArrayList<Message> result = new ArrayList<Message>();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestMessagesGetAll);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Message message=new Message();
                message.setId(rs.getInt("MessageId"));
                message.setCaption(rs.getString("MsgCaption"));
                message.setText(rs.getString("MsgText"));
                message.setUserId(rs.getInt("UserId"));
                message.setThreadId(rs.getInt("ThreadId"));
                java.util.Date date=new java.util.Date(
                        rs.getTimestamp("PostTime").getTime());
                message.setPostDate(date);
                result.add(message);
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return result;
    }

    private Integer generateUserId() throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestUserSequenceGetId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return null;
    }

    private Integer generateAuthorizationInfoId() throws DataAccessException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestAuthorizationInfoSequenceGetId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return null;
    }

    public void addUser(User user) throws DataAccessException {
        PreparedStatement userStat = null;
        PreparedStatement authStat = null;
        try {
            userStat = conn.prepareStatement(requestUserAdd);
            Integer userId = generateUserId();
            userStat.setInt(1, userId);
            userStat.setString(2, user.getName());
            java.sql.Date date = new Date(user.getRegistrationDate().getTime());
            userStat.setDate(3, date);
            userStat.setString(4, user.getInfo());
            userStat.executeUpdate();
            authStat = conn.prepareStatement(requestAuthorizationInfoAdd);
            Integer authId = generateAuthorizationInfoId();
            AuthorizationInfo authInfo = user.getAuthorizationInfo();
            authStat.setInt(1, authId);
            authStat.setInt(2, userId);
            authStat.setString(3, authInfo.getLogin());
            authStat.setString(4, authInfo.getPassword());
            authStat.executeUpdate();

            conn.commit();
            user.setId(userId);
            authInfo.setId(authId);
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(userStat);
            closeStatement(authStat);
        }
    }

    public void replaceUser(User user) throws DataAccessException {
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(requestUserUpdate);
            ps.setString(1, user.getName());
            java.sql.Date date=new Date(user.getRegistrationDate().getTime());
            ps.setDate(2, date);
            ps.setString(3, user.getInfo());
            ps.setInt(4, user.getId());
            ps.executeUpdate();
            replaceAuthorizationInfo(user.getAuthorizationInfo());

            conn.commit();
        } catch(SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

    public User getUserById(Integer id) throws DataAccessException {
        PreparedStatement userStat = null;        
        User user = null;
        try {
            userStat = conn.prepareStatement(requestUserGetById);
            userStat.setInt(1, id);
            ResultSet userRS = userStat.executeQuery();
            if (userRS.next()) {
                AuthorizationInfo info=getAuthInfoByUserId(id);

                user = new User();
                user.setId(id);
                user.setName(userRS.getString("UserName"));
                user.setInfo(userRS.getString("UserInfo"));
                user.setRegistrationDate(userRS.getDate("RegDate"));
                user.setAuthorizationInfo(info);
            }            
            conn.commit();
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(userStat);            
        }
        return user;
    }

    public void deleteUserById(Integer id) throws DataAccessException {
        PreparedStatement userStat = null;
        //PreparedStatement authStat = null;
        try {
            /*AuthorizationInfo info = getUserById(id).getAuthorizationInfo();
            authStat = conn.prepareStatement(requestAuthorizationInfoDeleteById);
            authStat.setInt(1, info.getId());
            authStat.executeUpdate();*/
            userStat = conn.prepareStatement(requestUserDeleteById);
            userStat.setInt(1, id);
            userStat.executeUpdate();

            conn.commit();
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(userStat);
            //closeStatement(authStat);
        }
    }

    public Collection<User> getUsers() throws DataAccessException {
        ArrayList<User> result=new ArrayList<User>();
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(requestUsersGetAll);
            ResultSet usersRS=ps.executeQuery();
            while(usersRS.next()) {
                User user=new User();
                int userId=usersRS.getInt("UserID");
                user.setId(userId);
                user.setName(usersRS.getString("UserName"));
                user.setInfo(usersRS.getString("UserInfo"));
                java.util.Date date=new java.util.Date(usersRS.getDate("RegDate").getTime());
                user.setRegistrationDate(date);
                user.setAuthorizationInfo(getAuthInfoByUserId(userId));
                result.add(user);
            }
        } catch(SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return result;
    }

    public void closeStore() throws DataAccessException {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                throw new DataAccessException(ex);
            }
        }
    }

    public Element toXMLElement() {
        throw new UnsupportedOperationException("You should'nt XMLize database");
    }

    public void loadFromXMLElement(Element data) {
        throw new UnsupportedOperationException("You should'nt deXMLize database");
    }

    private void closeStatement(Statement stat) {
        if (stat == null) {
            return;
        }
        try {
            stat.close();
        } catch (SQLException ex) {
        }
    }

    protected AuthorizationInfo getAuthInfoByUserId(int id) throws DataAccessException {
        AuthorizationInfo info = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(requestAuthorizationInfoGetByUserId);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                info = new AuthorizationInfo();
                info.setId(rs.getInt("AuthorizationId"));
                info.setUserId(id);
                info.setLogin(rs.getString("Login"));
                info.setPassword(rs.getString("Pass"));
            }
        } catch (SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
        return info;
    }

    protected void replaceAuthorizationInfo(AuthorizationInfo info) throws DataAccessException {
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(requestAuthorizationInfoUpdate);
            ps.setInt(1, info.getUserId());
            ps.setString(2, info.getLogin());
            ps.setString(3, info.getPassword());
            ps.setInt(4, info.getId());
            ps.executeUpdate();
            
            conn.commit();
        } catch(SQLException ex) {
            throw new DataAccessException(ex);
        } finally {
            closeStatement(ps);
        }
    }

}
